from Db_interface import DB_interface
import json
import requests
import time
import pymysql
import xlrd
import xlwt




class updata_data():
    def __init__(self):
        self.db = DB_interface().online_db()  # 线上数据库
        self.cursor = self.db.cursor()
        self.token = self.get_login()
        self.update_li = []

    def get_login(self):
        headers = {"Accept": "application/json", }
        url = 'https://api.xinhehonggu.com/api/auth/login'
        phone = '18866668888'
        password = 'xhhg2019'
        source = 'admin'
        data = {
            'method': '1',
            'phone': phone,
            'password': password,
            'source': source,
        }
        r = requests.post(url=url, data=data, headers=headers, verify=False)
        res_dict = r.json()
        token = res_dict['data']['token']
        return token

    def save_json_dict(self, paid_date):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '序号')
        sheet.write(0, 1, '申请号')
        sheet.write(0, 2, '类型数字')
        sheet.write(0, 3, '是否成功')
        sheet.write(0, 4, '信息')
        sheet.write(0, 5, '费用')
        if len(self.update_li) > 0:
            for i in range(len(self.update_li)):
                sheet.write(i + 1, 0, i + 1)
                sheet.write(i + 1, 1, self.update_li[i]['sqh'])
                sheet.write(i + 1, 2, self.update_li[i]['type'])
                sheet.write(i + 1, 3, self.update_li[i]['status'])
                sheet.write(i + 1, 4, self.update_li[i]['message'])
                sheet.write(i + 1, 5, self.update_li[i]['money_type'])
            workbook.save(f'./save_fee_dir/{paid_date}更新系统缴费状态.xls')

    def read_excel_new(self, all_item, paid_date):
        item_li = []
        data = xlrd.open_workbook('官费缴纳汇总表.xlsm')
        sheet_names = data.sheet_names()
        for sheet_name in sheet_names[:1]:
            table = data.sheet_by_name(sheet_name)
            for row in range(table.nrows):
                item = {}
                # 序号	申请号/专利号/国际申请号/海牙转交编号	业务类型	票据抬头	统一社会信用代码	费用种类	外币金额	费用金额（人民币）	备注
                row_datas = table.row_values(row)
                if '序号' not in row_datas and row_datas[0]:
                    print(row_datas[0])
                    item['serial_number'] = str(int(row_datas[0]))
                    item['application_number'] = row_datas[1]
                    item['money_type'] = row_datas[5]
                    item['money_number'] = row_datas[7]
                    item['close_date'] = ''
                    now_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                    item['update_date'] = now_time  # 例如：'2020-12-24 17:00:00'
                    item['paid_date'] = paid_date
                    item_li.append(item)
        all_item['item_li'] = item_li
        return all_item

    def update_db(self, all_item):
        print('===================================开始插入数据库===================================')
        for item in all_item['item_li']:
            self.upload_to_db(item)  # 插入数据库
        print('插入数据库 ccy_paid 成功!')

    def upload_to_db(self, item):
        sql = "insert into ccy_paid (serial_number,application_number,money_type,money_number,close_date,update_date,paid_date) values('{}','{}','{}','{}','{}','{}','{}')".format(
            item['serial_number'], item['application_number'], item['money_type'], item['money_number'], item['close_date'],
            item['update_date'], item['paid_date'])
        self.cursor.execute(sql)
        self.db.commit()

    def juage_paid_date(self, paid_date):
        paid_date_li = self.get_paid_date()
        if paid_date in paid_date_li:
            print('请核对缴费日期是否正确')
            return False
        return True

    def get_paid_date(self):
        sql = f"select paid_date from ccy_paid group by paid_date"
        self.cursor.execute(sql)
        content = self.cursor.fetchall()
        paid_date_li = []
        for i in content:
            paid_date_li.append(i[0])
        return paid_date_li

    def get_paid_sqh(self, paid_date):
        sql = f"select application_number from ccy_paid where paid_date='{paid_date}'"
        self.cursor.execute(sql)
        content = self.cursor.fetchall()
        paid_sqh_li = []
        for i in content:
            paid_sqh_li.append(i[0])
        return list(set(paid_sqh_li))

    def get_paid_detail(self, sqh, paid_date):
        sql = f"select * from ccy_paid where application_number='{sqh}' and paid_date= '{paid_date}' and money_type NOT LIKE '%滞纳金%'"
        self.cursor.execute(sql)
        content = self.cursor.fetchall()
        return content

    def update_retry(self, num, url, headers, item):
        if num < 10:
            try:
                num += 1
                response = requests.post(url, headers=headers, data=item)

                print('response:', response.json())
                return json.loads(response.content.decode())
            except Exception as e:
                print('重试第{}次,{}'.format(num, e))
                time.sleep(2)
                self.update_retry(num, url, headers, item)

    def update_xhhg_system(self, item, token):
        headers = {"Accept": "application/json", "authorization": "Bearer " + token}
        # url = f"http://test.api.whxhhgip.com/api/admin/financialManagement/payRecord/paySuccess/country"
        url = f"http://api.xinhehonggu.com/api/admin/financialManagement/payRecord/paySuccess/country/"
        num = 0
        if item['type'] == "2":         # type = "2" 为传年费参数
            num_type = item['money_type'].split('+')

            if num_type[0] in "年年费":
                sss = [i for i in num_type if "年年费" in i][0]
                item['num'] = sss.split("年年费")[0] + '年年费'

        print("data: ", item)
        json_dict = self.update_retry(num, url, headers, item)
        print(json_dict)
        new_item = {}
        new_item['sqh'] = item['applicationNumber']
        new_item['type'] = item['type']
        new_item['status'] = json_dict['status']
        new_item['message'] = json_dict['message'] if 'message' in json_dict.keys() else ''
        new_item['money_type'] = item['money_type']
        if json_dict['code'] == 400:
            self.update_li.append(new_item)

    def get_type_item(self, all_item, paid_date):
        print(paid_date)
        paid_sqh_li = self.get_paid_sqh(paid_date)
        type_item_li = []
        for sqh in paid_sqh_li:
            type_item = {}
            content = self.get_paid_detail(sqh, paid_date)

            for i in content:
                type = ''
                money_type = i[3] + str(int(float(i[4])))
                if '年费' in money_type:
                    type = '2'
                elif '滞纳金' in money_type:
                    type = '2'
                elif '著录事项变更费' in money_type:
                    type = '3'
                elif '申请费' in money_type:
                    type = '1'
                elif '优先权' in money_type:
                    type = '1'
                elif '专利文件副本证明费' in money_type:
                    type = '1'
                elif '公布印刷费' in money_type:
                    type = '1'
                elif '实质审查费' in money_type:
                    type = '1'
                else:
                    print('特殊情况')
                    if '恢复权利请求费' in money_type:
                        type = '2'

                type_item['type'] = type
                type_item['applicationNumber'] = 'ZL' + sqh
                type_item['money_type'] = money_type
                type_item['pay_country_at'] = paid_date
                type_item['num'] = i[3]

                self.update_xhhg_system(type_item, self.token)

        self.save_json_dict(paid_date)


    def main_funm(self):
        all_item = {}
        paid_date = time.strftime("%Y-%m-%d", time.localtime())
        # paid_date = '2025-07-01'  # 日期更改为当天
        flag = self.juage_paid_date(paid_date)  # 检查数据库中是否有该条日期
        if flag:
            all_item = self.read_excel_new(all_item, paid_date)
            self.update_db(all_item)  # 入库
            self.get_type_item(all_item, paid_date)


if __name__ == '__main__':
    updata_data().main_funm()